import pymysql


def get_mysql_connection(database='ali'):
    conn = pymysql.connect(host='localhost', port=3306,
                           user='root', password='123456',
                           database=database, charset='utf8mb4')
    return conn


def get_stock_data(start, end):
    """
    根据时间段获取股票数据
    :param start: 开始时间
    :param end: 截止时间
    :return: 股票数据。data_x 日期列表；data_y 数据列表，一条数据包含 开盘 收盘 最低 最高价
    """
    data_y = []
    data_x = []
    conn = get_mysql_connection()
    try:
        with conn.cursor() as cursor:
            sql = '''
                select s_open as open, s_close as close, s_low as lowest, s_high as highest 
                from tb_shares
                where s_datetime between %s and %s
            '''
            cursor.execute(sql, (start, end))

            for d in iter(lambda: cursor.fetchone(), None):
                data_y.append(list(map(float, d)))

            sql = 'select s_datetime as datetime from tb_shares where s_datetime between %s and %s'
            cursor.execute(sql, (start, end))

            for d in iter(lambda: cursor.fetchone(), None):
                data_x.append(d[0].strftime('%Y-%m-%d'))
    except pymysql.MySQLError as e:
        print('查询错误：', e)
    finally:
        conn.close()

    return data_x, data_y


def get_order_main_data():
    # 获取电商订单相关信息
    gmv = 0
    sales = 0
    real_sales = 0
    urg = 0
    conn = get_mysql_connection(database='data_viz')
    try:
        with conn.cursor() as cursor:
            cursor.execute('select sum(orderAmount) from tb_order')
            gmv = round(float(cursor.fetchone()[0]) / 10000, 2)
            cursor.execute('select sum(payment) from tb_order')
            sales = round(float(cursor.fetchone()[0]) / 10000, 2)
            cursor.execute('select sum(payment) from tb_order where chargeback="否"')
            real_sales = round(float(cursor.fetchone()[0]) / 10000, 2)
            cursor.execute('select sum(payment)/count(distinct userid) from tb_order where chargeback="否"')
            urg = round(float(cursor.fetchone()[0]), 2)

    except pymysql.MySQLError as e:
        print('查询错误：', e)
    finally:
        conn.close()

    return {
        'results': [
            {'name': 'GMV(交易流水)', 'value': gmv, 'unit': '万元'},
            {'name': '销售额', 'value': sales, 'unit': '万元'},
            {'name': '实际销售额', 'value': real_sales, 'unit': '万元'},
            {'name': '客单价', 'value': urg, 'unit': '元'},
        ]
    }


def get_order_month_gmv_data():
    # 获取电商订单相关信息
    month = []
    gmv_data = []
    conn = get_mysql_connection(database='data_viz')
    try:
        with conn.cursor() as cursor:
            cursor.execute('''
                select month(paytime),sum(orderAmount) 
                from tb_order
                group by month(paytime)
            ''')

            # for d in iter(lambda: cursor.fetchone(), None):
            for i in iter(lambda: cursor.fetchone(), None):
                month.append(int(i[0]))
                gmv_data.append(round(float(i[-1]) / 10000, 2))


    except pymysql.MySQLError as e:
        print('查询错误：', e)
    finally:
        conn.close()

    return {
        'month': month,
        'gmv_data': gmv_data,
    }


def get_order_month_paynum_data():
    # 获取电商订单相关信息
    month = []
    num_data = []
    conn = get_mysql_connection(database='data_viz')
    try:
        with conn.cursor() as cursor:
            cursor.execute('''
                select month(paytime),count(*) 
                from tb_order
                group by month(paytime);
            ''')

            # for d in iter(lambda: cursor.fetchone(), None):
            for i in iter(lambda: cursor.fetchone(), None):
                month.append(int(i[0]))
                num_data.append(int(i[-1]))


    except pymysql.MySQLError as e:
        print('查询错误：', e)
    finally:
        conn.close()

    return {
        'month': month,
        'num_data': num_data,
    }


def get_order_platform_data():
    # 获取电商订单相关信息
    platformType = []
    nums = []
    res = []
    conn = get_mysql_connection(database='data_viz')
    try:
        with conn.cursor() as cursor:
            cursor.execute('''
                select platformType,count(distinct userid)
                from tb_order
                group by platformType;
            ''')

            # for d in iter(lambda: cursor.fetchone(), None):
            for i in iter(lambda: cursor.fetchone(), None):
                res.append({'name': i[0], 'value': int(i[-1])})


    except pymysql.MySQLError as e:
        print('查询错误：', e)
    finally:
        conn.close()

    return {
        'res': res
    }


def get_order_line2_data():
    # 柱状图2：按月展示每个平台的gmv，销售量
    datas = []
    conn = get_mysql_connection(database='data_viz')
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute('''
                select month(paytime) as months,sum(payment) as gmv,platformType
                from tb_order
                group by month(paytime),platformType
                having platformType in ('alimp','app','web','wechatmp')
            ''')

            datas = cursor.fetchall()
            for data in datas:
                data['gmv'] = round(float(data.get('gmv', 0)) / 10000, 2)


    except pymysql.MySQLError as e:
        print('查询错误：', e)
    finally:
        conn.close()

    platform = ['alimp', 'app', 'web', 'wechatmp']

    return {
        'res': [{'platformType': name, 'data': [data['gmv'] for data in datas if data['platformType'] == name]} for name
                in platform],
        'months': [f'{i}月' for i in range(1,13)]

    }
